﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[vwMSX_servers_waiting]
#-- Purpose:		Filters tblMSX_server_discovery to unconfigured servers only
#--	Last Update:	04/28/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE VIEW [dbo].[vwMSX_servers_waiting]
AS

SELECT		ROW_NUMBER() OVER(	PARTITION BY	ssd.ssd_type_ro, 
												sql.sql_version_major_ro
								ORDER BY		ssd.ssd_type_ro, 
												sql.sql_version_major_ro,
												ssd.ssd_sqlserver_name_ro
							) rownum,
			ssd.ssd_type_ro,
			sql.sql_edition_ro,
			ssd.ssd_sqlserver_name_ro,
			app.[app_name] sql_description, 
			sql.sql_product_version,
			sql.sql_has_local_jobs,
			sql.sql_has_master_jobs,
			sql.sql_is_mixed_mode,
			sql.sql_backup_directory,
			sql.sql_version_major_ro,
			sql.sql_version_minor_ro
FROM		[dbo].[tblMSX_server_discovery] ssd
JOIN		[dbo].[tblMSX_server_sql_info] sql ON ssd.ssd_id = sql.sql_ssd_id
LEFT JOIN	[dbo].[tblMSX_application] app ON ssd.[ssd_app_id] = app.[app_id]
WHERE		ssd.ssd_is_disabled = 0
			AND ssd.ssd_is_vendor_managed_ro = 0
			AND (
				(
					sql.sql_has_master_jobs = 0
					AND ssd.ssd_type_ro <> 'MSX'
					AND sql.sql_version_major_ro >= 9
				)
				OR ISNULL(sql.sql_backup_server_ro, '') = ''
			)